{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dressmaker - Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "Assuming that any garment could be made in any of the available materials, list the garments (description, fabric, colour and pattern) which are expensive to make, that is, those for which the labour costs are 80% or more of the total cost."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>description</th>\n",
       "        <th>fabric</th>\n",
       "        <th>colour</th>\n",
       "        <th>pattern</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Trousers</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Yellow Stripe</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Long Skirt</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Yellow Stripe</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Short Skirt</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Yellow Stripe</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sundress</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Yellow Stripe</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Trousers</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Green Stripe</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Long Skirt</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Green Stripe</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Short Skirt</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Green Stripe</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sundress</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Green Stripe</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Trousers</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Black Dotted</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Long Skirt</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Black Dotted</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Short Skirt</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Black Dotted</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sundress</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Black Dotted</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Trousers</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Red Stripe</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Long Skirt</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Red Stripe</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Short Skirt</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Red Stripe</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sundress</td>\n",
       "        <td>Cotton</td>\n",
       "        <td>Red Stripe</td>\n",
       "        <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Trousers</td>\n",
       "        <td>Polyester</td>\n",
       "        <td>Pale Yellow</td>\n",
       "        <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Long Skirt</td>\n",
       "        <td>Polyester</td>\n",
       "        <td>Pale Yellow</td>\n",
       "        <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Short Skirt</td>\n",
       "        <td>Polyester</td>\n",
       "        <td>Pale Yellow</td>\n",
       "        <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sundress</td>\n",
       "        <td>Polyester</td>\n",
       "        <td>Pale Yellow</td>\n",
       "        <td>Printed</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">38 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Trousers', 'Cotton', 'Yellow Stripe', 'Woven'),\n",
       " ('Long Skirt', 'Cotton', 'Yellow Stripe', 'Woven'),\n",
       " ('Short Skirt', 'Cotton', 'Yellow Stripe', 'Woven'),\n",
       " ('Sundress', 'Cotton', 'Yellow Stripe', 'Woven'),\n",
       " ('Trousers', 'Cotton', 'Green Stripe', 'Woven'),\n",
       " ('Long Skirt', 'Cotton', 'Green Stripe', 'Woven'),\n",
       " ('Short Skirt', 'Cotton', 'Green Stripe', 'Woven'),\n",
       " ('Sundress', 'Cotton', 'Green Stripe', 'Woven'),\n",
       " ('Trousers', 'Cotton', 'Black Dotted', 'Woven'),\n",
       " ('Long Skirt', 'Cotton', 'Black Dotted', 'Woven'),\n",
       " ('Short Skirt', 'Cotton', 'Black Dotted', 'Woven'),\n",
       " ('Sundress', 'Cotton', 'Black Dotted', 'Woven'),\n",
       " ('Trousers', 'Cotton', 'Red Stripe', 'Woven'),\n",
       " ('Long Skirt', 'Cotton', 'Red Stripe', 'Woven'),\n",
       " ('Short Skirt', 'Cotton', 'Red Stripe', 'Woven'),\n",
       " ('Sundress', 'Cotton', 'Red Stripe', 'Woven'),\n",
       " ('Trousers', 'Polyester', 'Pale Yellow', 'Printed'),\n",
       " ('Long Skirt', 'Polyester', 'Pale Yellow', 'Printed'),\n",
       " ('Short Skirt', 'Polyester', 'Pale Yellow', 'Printed'),\n",
       " ('Sundress', 'Polyester', 'Pale Yellow', 'Printed'),\n",
       " ('Trousers', 'Cotton', 'Blue Stripe', 'Woven'),\n",
       " ('Long Skirt', 'Cotton', 'Blue Stripe', 'Woven'),\n",
       " ('Short Skirt', 'Cotton', 'Blue Stripe', 'Woven'),\n",
       " ('Sundress', 'Cotton', 'Blue Stripe', 'Woven'),\n",
       " ('Trousers', 'Cotton', 'Pink Check', 'Woven'),\n",
       " ('Long Skirt', 'Cotton', 'Pink Check', 'Woven'),\n",
       " ('Short Skirt', 'Cotton', 'Pink Check', 'Woven'),\n",
       " ('Sundress', 'Cotton', 'Pink Check', 'Woven'),\n",
       " ('Trousers', 'Rayon', 'Red/Orange', 'Printed'),\n",
       " ('Sundress', 'Rayon', 'Red/Orange', 'Printed'),\n",
       " ('Trousers', 'Cotton', 'Blue Abstract', 'Printed'),\n",
       " ('Long Skirt', 'Cotton', 'Blue Abstract', 'Printed'),\n",
       " ('Short Skirt', 'Cotton', 'Blue Abstract', 'Printed'),\n",
       " ('Sundress', 'Cotton', 'Blue Abstract', 'Printed'),\n",
       " ('Trousers', 'Cotton', 'Green Abstract', 'Printed'),\n",
       " ('Long Skirt', 'Cotton', 'Green Abstract', 'Printed'),\n",
       " ('Short Skirt', 'Cotton', 'Green Abstract', 'Printed'),\n",
       " ('Sundress', 'Cotton', 'Green Abstract', 'Printed')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT description, fabric, colour, pattern\n",
    "  FROM garment, material\n",
    "    WHERE garment.labour_cost/(garment.labour_cost+material.cost) > 0.8"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "List the descriptions and the number of orders of the less popular garments, that is those for which less than the average number of orders per garment have been placed. Also print out the average number of orders per garment. When calculating the average, ignore any garments for which no orders have been made."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>description</th>\n",
       "        <th>n_orders</th>\n",
       "        <th>avg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shorts</td>\n",
       "        <td>5</td>\n",
       "        <td>5.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Short Skirt</td>\n",
       "        <td>5</td>\n",
       "        <td>5.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sundress</td>\n",
       "        <td>5</td>\n",
       "        <td>5.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Suntop</td>\n",
       "        <td>4</td>\n",
       "        <td>5.17</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Shorts', 5, 5.17),\n",
       " ('Short Skirt', 5, 5.17),\n",
       " ('Sundress', 5, 5.17),\n",
       " ('Suntop', 4, 5.17)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT style_no, description, COUNT(order_ref) n_orders\n",
    "      FROM garment JOIN order_line ON (garment.style_no=order_line.ol_style)\n",
    "        GROUP BY style_no, description\n",
    ")\n",
    "SELECT description, n_orders, ROUND(avg_orders, 2) avg\n",
    "  FROM t JOIN (\n",
    "      SELECT AVG(n_orders) avg_orders FROM t WHERE n_orders>0\n",
    "  ) a \n",
    "    WHERE n_orders < avg_orders"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "Which is the most popular line, that is, the garment with the highest number of orders. Bearing in mind the fact that there may be several such garments, list the garment description(s) and number(s) of orders."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>description</th>\n",
       "        <th>n_orders</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Trousers</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Long Skirt</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Trousers', 6), ('Long Skirt', 6)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT style_no, description, COUNT(order_ref) n_orders\n",
    "      FROM garment JOIN order_line ON (\n",
    "          garment.style_no=order_line.ol_style)\n",
    "          GROUP BY style_no, description\n",
    ")\n",
    "SELECT description, t.n_orders\n",
    "  FROM t JOIN (\n",
    "      SELECT DISTINCT n_orders FROM t \n",
    "      ORDER BY n_orders DESC LIMIT 1) a ON (\n",
    "      t.n_orders=a.n_orders)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "List the descriptions, and costs of the more expensive size 8, Cotton garments which might be ordered, that is those costing more than the average (labour costs + material costs) to make."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>description</th>\n",
       "        <th>material_no</th>\n",
       "        <th>total_cost</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sundress</td>\n",
       "        <td>14</td>\n",
       "        <td>31.2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Sundress', 14, 31.2)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT style_no, material_no, description, ol_size, fabric, \n",
    "      labour_cost+quantity*cost tot_cost\n",
    "      FROM garment JOIN order_line ON (\n",
    "          order_line.ol_style=garment.style_no) JOIN \n",
    "        material ON (\n",
    "          material.material_no=order_line.ol_material) JOIN\n",
    "        quantities ON (\n",
    "            quantities.size_q=order_line.ol_size AND \n",
    "            quantities.style_q=order_line.ol_style)\n",
    ")\n",
    "SELECT description, material_no, ROUND(tot_cost, 2) total_cost\n",
    "  FROM t JOIN (SELECT AVG(tot_cost) avg_cost FROM t) a\n",
    "    WHERE ol_size=8 AND LOWER(fabric)='cotton' AND \n",
    "    tot_cost>avg_cost"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "What is the most common size ordered for each garment type? List description, size and number of orders, assuming that there could be several equally popular sizes for each type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>description</th>\n",
       "        <th>ol_size</th>\n",
       "        <th>n_orders</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Trousers</td>\n",
       "        <td>8</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Long Skirt</td>\n",
       "        <td>8</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shorts</td>\n",
       "        <td>16</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shorts</td>\n",
       "        <td>18</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shorts</td>\n",
       "        <td>14</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shorts</td>\n",
       "        <td>10</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shorts</td>\n",
       "        <td>8</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Short Skirt</td>\n",
       "        <td>10</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sundress</td>\n",
       "        <td>10</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Suntop</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Trousers', 8, 3),\n",
       " ('Long Skirt', 8, 3),\n",
       " ('Shorts', 16, 1),\n",
       " ('Shorts', 18, 1),\n",
       " ('Shorts', 14, 1),\n",
       " ('Shorts', 10, 1),\n",
       " ('Shorts', 8, 1),\n",
       " ('Short Skirt', 10, 2),\n",
       " ('Sundress', 10, 2),\n",
       " ('Suntop', 12, 2)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT style_no, description, ol_size, COUNT(*) n_orders,\n",
    "      RANK() OVER (PARTITION BY style_no ORDER BY COUNT(*) DESC) rank\n",
    "      FROM garment JOIN order_line ON (\n",
    "          order_line.ol_style=garment.style_no)\n",
    "          GROUP BY style_no, ol_size, description\n",
    ")\n",
    "SELECT description, ol_size, n_orders\n",
    "    FROM t\n",
    "    WHERE rank=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
